from flask import Blueprint, request, jsonify
from flask_jwt_extended import jwt_required, get_jwt_identity
from app.models import db
from app.models.user import User
from sqlalchemy import text
from datetime import datetime

material_categories_bp = Blueprint('material_categories', __name__)

@material_categories_bp.route('', methods=['GET'])
def get_categories():
    """获取材料分类树"""
    try:
        # 获取所有分类
        query = text("""
            SELECT id, name, code, parent_id, level, path, sort_order, 
                   description, is_active, created_at, updated_at
            FROM material_categories 
            WHERE is_active = 1
            ORDER BY level ASC, sort_order ASC, id ASC
        """)
        
        result = db.session.execute(query)
        categories = []
        
        for row in result:
            categories.append({
                'id': row.id,
                'name': row.name,
                'code': row.code,
                'parent_id': row.parent_id,
                'level': row.level,
                'path': row.path,
                'sort_order': row.sort_order,
                'description': row.description,
                'is_active': bool(row.is_active),
                'created_at': row.created_at.isoformat() if row.created_at and hasattr(row.created_at, 'isoformat') else row.created_at,
                'updated_at': row.updated_at.isoformat() if row.updated_at and hasattr(row.updated_at, 'isoformat') else row.updated_at,
                'children': []
            })
        
        # 构建树形结构
        category_map = {cat['id']: cat for cat in categories}
        tree = []
        
        for category in categories:
            if category['parent_id'] is None:
                tree.append(category)
            else:
                parent = category_map.get(category['parent_id'])
                if parent:
                    parent['children'].append(category)
        
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': {
                'categories': tree,
                'total': len(categories)
            }
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/tree', methods=['GET'])
def get_categories_tree():
    """获取材料分类树形结构"""
    try:
        # 获取所有分类
        query = text("""
            SELECT id, name, code, parent_id, level, path, sort_order, 
                   description, is_active, created_at, updated_at
            FROM material_categories 
            WHERE is_active = 1
            ORDER BY level ASC, sort_order ASC, id ASC
        """)
        
        result = db.session.execute(query)
        categories = []
        
        for row in result:
            categories.append({
                'id': row.id,
                'name': row.name,
                'code': row.code,
                'parent_id': row.parent_id,
                'level': row.level,
                'path': row.path,
                'sort_order': row.sort_order,
                'description': row.description,
                'is_active': bool(row.is_active),
                'created_at': row.created_at.isoformat() if row.created_at and hasattr(row.created_at, 'isoformat') else row.created_at,
                'updated_at': row.updated_at.isoformat() if row.updated_at and hasattr(row.updated_at, 'isoformat') else row.updated_at,
                'children': []
            })
        
        # 构建树形结构
        category_map = {cat['id']: cat for cat in categories}
        tree = []
        
        for category in categories:
            if category['parent_id'] is None:
                tree.append(category)
            else:
                parent = category_map.get(category['parent_id'])
                if parent:
                    parent['children'].append(category)
        
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': tree
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/stats', methods=['GET'])
def get_category_stats():
    """获取分类统计信息"""
    try:
        # 获取统计数据
        stats_query = text("""
            SELECT 
                COUNT(*) as total_categories,
                COUNT(CASE WHEN mc.is_active = 1 THEN 1 END) as active_categories,
                MAX(mc.level) as max_level,
                COUNT(DISTINCT CASE WHEN m.id IS NOT NULL THEN mc.id END) as categories_with_materials
            FROM material_categories mc
            LEFT JOIN materials m ON m.category_id = mc.id
        """)
        
        result = db.session.execute(stats_query)
        row = result.fetchone()
        
        stats = {
            'total_categories': row.total_categories or 0,
            'active_categories': row.active_categories or 0,
            'max_level': row.max_level or 0,
            'categories_with_materials': row.categories_with_materials or 0
        }
        
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': stats
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/flat', methods=['GET'])
def get_categories_flat():
    """获取扁平化的分类列表"""
    try:
        level = request.args.get('level', type=int)
        parent_id = request.args.get('parent_id', type=int)
        
        conditions = ['is_active = 1']
        params = {}
        
        if level is not None:
            conditions.append('level = :level')
            params['level'] = level
        
        if parent_id is not None:
            conditions.append('parent_id = :parent_id')
            params['parent_id'] = parent_id
        
        where_clause = 'WHERE ' + ' AND '.join(conditions)
        
        query = text(f"""
            SELECT id, name, code, parent_id, level, path, sort_order, description
            FROM material_categories 
            {where_clause}
            ORDER BY sort_order ASC, id ASC
        """)
        
        result = db.session.execute(query, params)
        categories = []
        
        for row in result:
            categories.append({
                'id': row.id,
                'name': row.name,
                'code': row.code,
                'parent_id': row.parent_id,
                'level': row.level,
                'path': row.path,
                'sort_order': row.sort_order,
                'description': row.description
            })
        
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': {'categories': categories}
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/manage', methods=['GET'])
@jwt_required()
def get_categories_manage():
    """获取分类管理列表（管理员用）"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role not in ['admin', 'manager']:
            return jsonify({'error': '权限不足'}), 403
        
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        search = request.args.get('search', '')
        level = request.args.get('level', type=int)
        
        # 构建查询条件
        conditions = []
        params = {}
        
        if search:
            conditions.append('(name LIKE :search OR code LIKE :search)')
            params['search'] = f'%{search}%'
        
        if level is not None:
            conditions.append('level = :level')
            params['level'] = level
        
        where_clause = 'WHERE ' + ' AND '.join(conditions) if conditions else ''
        
        # 查询总数
        count_query = text(f"""
            SELECT COUNT(*) as total
            FROM material_categories 
            {where_clause}
        """)
        
        total_result = db.session.execute(count_query, params)
        total = total_result.scalar()
        
        # 查询数据
        offset = (page - 1) * per_page
        data_query = text(f"""
            SELECT mc.id, mc.name, mc.code, mc.parent_id, mc.level, mc.path, 
                   mc.sort_order, mc.description, mc.is_active, mc.created_at, mc.updated_at,
                   parent.name as parent_name,
                   COUNT(m.id) as material_count
            FROM material_categories mc
            LEFT JOIN material_categories parent ON mc.parent_id = parent.id
            LEFT JOIN materials m ON m.category_id = mc.id
            {where_clause}
            GROUP BY mc.id, mc.name, mc.code, mc.parent_id, mc.level, mc.path, 
                     mc.sort_order, mc.description, mc.is_active, mc.created_at, mc.updated_at,
                     parent.name
            ORDER BY mc.level ASC, mc.sort_order ASC, mc.id ASC
            LIMIT :per_page OFFSET :offset
        """)
        
        params.update({'per_page': per_page, 'offset': offset})
        result = db.session.execute(data_query, params)
        
        categories = []
        for row in result:
            categories.append({
                'id': row.id,
                'name': row.name,
                'code': row.code,
                'parent_id': row.parent_id,
                'parent_name': row.parent_name,
                'level': row.level,
                'path': row.path,
                'sort_order': row.sort_order,
                'description': row.description,
                'is_active': bool(row.is_active),
                'material_count': row.material_count,
                'created_at': row.created_at.isoformat() if row.created_at and hasattr(row.created_at, 'isoformat') else row.created_at,
                'updated_at': row.updated_at.isoformat() if row.updated_at and hasattr(row.updated_at, 'isoformat') else row.updated_at
            })
        
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': {
                'categories': categories,
                'total': total,
                'page': page,
                'per_page': per_page,
                'pages': (total + per_page - 1) // per_page
            }
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('', methods=['POST'])
@jwt_required()
def create_category():
    """创建材料分类"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role not in ['admin', 'manager']:
            return jsonify({'error': '权限不足'}), 403
        
        data = request.get_json()
        
        # 验证必填字段
        required_fields = ['name', 'code']
        for field in required_fields:
            if not data.get(field):
                return jsonify({'error': f'{field} 不能为空'}), 400
        
        # 检查编码是否已存在
        check_query = text("""
            SELECT COUNT(*) as count
            FROM material_categories 
            WHERE code = :code
        """)
        
        result = db.session.execute(check_query, {'code': data['code']})
        if result.scalar() > 0:
            return jsonify({'error': '分类编码已存在'}), 400
        
        # 处理父分类和层级
        parent_id = data.get('parent_id')
        level = 1
        path = data['code']
        
        if parent_id:
            parent_query = text("""
                SELECT level, path FROM material_categories WHERE id = :parent_id
            """)
            parent_result = db.session.execute(parent_query, {'parent_id': parent_id})
            parent_row = parent_result.fetchone()
            
            if not parent_row:
                return jsonify({'error': '父分类不存在'}), 400
            
            level = parent_row.level + 1
            path = f"{parent_row.path}/{data['code']}"
            
            # 检查层级限制（最多3级）
            if level > 3:
                return jsonify({'error': '分类层级不能超过3级'}), 400
        
        # 插入新分类
        insert_query = text("""
            INSERT INTO material_categories 
            (name, code, parent_id, level, path, sort_order, description, 
             is_active, created_at, updated_at)
            VALUES (:name, :code, :parent_id, :level, :path, :sort_order, 
                    :description, :is_active, :created_at, :updated_at)
        """)
        
        now = datetime.now()
        db.session.execute(insert_query, {
            'name': data['name'],
            'code': data['code'],
            'parent_id': parent_id,
            'level': level,
            'path': path,
            'sort_order': data.get('sort_order', 0),
            'description': data.get('description'),
            'is_active': data.get('is_active', True),
            'created_at': now,
            'updated_at': now
        })
        
        db.session.commit()
        
        return jsonify({
            'code': 201,
            'message': '分类创建成功'
        }), 201
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/<int:category_id>', methods=['PUT'])
@jwt_required()
def update_category(category_id):
    """更新材料分类"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role not in ['admin', 'manager']:
            return jsonify({'error': '权限不足'}), 403
        
        data = request.get_json()
        
        # 检查分类是否存在
        check_query = text("""
            SELECT id FROM material_categories WHERE id = :id
        """)
        
        result = db.session.execute(check_query, {'id': category_id})
        if not result.fetchone():
            return jsonify({'error': '分类不存在'}), 404
        
        # 如果更新编码，检查是否重复
        if 'code' in data:
            code_check_query = text("""
                SELECT COUNT(*) as count
                FROM material_categories 
                WHERE code = :code AND id != :id
            """)
            
            result = db.session.execute(code_check_query, {
                'code': data['code'],
                'id': category_id
            })
            
            if result.scalar() > 0:
                return jsonify({'error': '分类编码已存在'}), 400
        
        # 构建更新字段
        update_fields = []
        params = {'id': category_id, 'updated_at': datetime.now()}
        
        allowed_fields = ['name', 'code', 'sort_order', 'description', 'is_active']
        for field in allowed_fields:
            if field in data:
                update_fields.append(f'{field} = :{field}')
                params[field] = data[field]
        
        if not update_fields:
            return jsonify({'error': '没有可更新的字段'}), 400
        
        update_fields.append('updated_at = :updated_at')
        
        # 执行更新
        update_query = text(f"""
            UPDATE material_categories 
            SET {', '.join(update_fields)}
            WHERE id = :id
        """)
        
        db.session.execute(update_query, params)
        
        # 如果更新了编码，需要更新路径
        if 'code' in data:
            update_path_query = text("""
                UPDATE material_categories 
                SET path = CASE 
                    WHEN parent_id IS NULL THEN :new_code
                    ELSE (SELECT CONCAT(parent.path, '/', :new_code) 
                          FROM material_categories parent 
                          WHERE parent.id = material_categories.parent_id)
                END,
                updated_at = :updated_at
                WHERE id = :id
            """)
            
            db.session.execute(update_path_query, {
                'new_code': data['code'],
                'id': category_id,
                'updated_at': datetime.now()
            })
        
        db.session.commit()
        
        return jsonify({
            'code': 200,
            'message': '分类更新成功'
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/<int:category_id>', methods=['DELETE'])
@jwt_required()
def delete_category(category_id):
    """删除材料分类"""
    try:
        current_user_id = get_jwt_identity()
        user = User.query.get(current_user_id)
        
        if not user or user.role != 'admin':
            return jsonify({'error': '权限不足'}), 403
        
        # 检查是否有子分类
        children_query = text("""
            SELECT COUNT(*) as count
            FROM material_categories 
            WHERE parent_id = :parent_id
        """)
        
        result = db.session.execute(children_query, {'parent_id': category_id})
        if result.scalar() > 0:
            return jsonify({'error': '该分类下还有子分类，无法删除'}), 400
        
        # 检查是否有关联的材料
        materials_query = text("""
            SELECT COUNT(*) as count
            FROM materials 
            WHERE category_id = :category_id
        """)
        
        result = db.session.execute(materials_query, {'category_id': category_id})
        if result.scalar() > 0:
            return jsonify({'error': '该分类下还有材料，无法删除'}), 400
        
        # 删除分类
        delete_query = text("""
            DELETE FROM material_categories WHERE id = :id
        """)
        
        db.session.execute(delete_query, {'id': category_id})
        db.session.commit()
        
        return jsonify({
            'code': 200,
            'message': '分类删除成功'
        }), 200
        
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': str(e)}), 500

@material_categories_bp.route('/<int:category_id>/materials', methods=['GET'])
def get_category_materials(category_id):
    """获取分类下的材料列表"""
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 20, type=int)
        
        # 查询总数
        count_query = text("""
            SELECT COUNT(*) as total
            FROM materials 
            WHERE category_id = :category_id
        """)
        
        total_result = db.session.execute(count_query, {'category_id': category_id})
        total = total_result.scalar()
        
        # 查询材料数据
        offset = (page - 1) * per_page
        materials_query = text("""
            SELECT id, name, code, category, stock_quantity, min_stock, unit, price
            FROM materials 
            WHERE category_id = :category_id
            ORDER BY name ASC
            LIMIT :per_page OFFSET :offset
        """)
        
        result = db.session.execute(materials_query, {
            'category_id': category_id,
            'per_page': per_page,
            'offset': offset
        })
        
        materials = []
        for row in result:
            materials.append({
                'id': row.id,
                'name': row.name,
                'code': row.code,
                'category': row.category,
                'stock_quantity': row.stock_quantity,
                'min_stock': row.min_stock,
                'unit': row.unit,
                'price': float(row.price) if row.price else None
            })
        
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': {
                'materials': materials,
                'total': total,
                'page': page,
                'per_page': per_page,
                'pages': (total + per_page - 1) // per_page
            }
        }), 200
        
    except Exception as e:
        return jsonify({'error': str(e)}), 500